<html>
	<head>
		<meta http-equiv="Content-Type" content="text/html; charset=utf-8"> 
		<meta http-equiv="Content-Style-Type" content="text/css"> 
		<meta http-equiv="Content-Script-Type" content="text/javascript"> 
		<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js"></script>
		<script src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.16/jquery-ui.min.js" type="text/javascript"></script>
		<script src="http://jquery-ui.googlecode.com/svn/tags/latest/external/jquery.bgiframe-2.1.2.js" type="text/javascript"></script>
		<script src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.16/i18n/jquery-ui-i18n.min.js" type="text/javascript"></script>
		<link rel="stylesheet" href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.16/themes/base/jquery-ui.css" type="text/css" media="all" />
		<link rel="stylesheet" href="http://static.jquery.com/ui/css/demo-docs-theme/ui.theme.css" type="text/css" media="all" />
		<link rel="stylesheet" href="style.css" type="text/css" media="screen" /> 
		<script type="text/javascript" src="/redirect/TestNew/tiny_mce/tiny_mce.js" ></script >
<script type="text/javascript">
$(document).ready(function()
{
	var bindbehaviors = function(scope) 
	{
		$(".datepicker").datepicker();
		$(".datepicker").datepicker($.datepicker.regional['en']);
  		$("a.tab").click(function () {  
  
        // switch all tabs off  
        $(".active").removeClass("active");  
  
        // switch this tab on  
        $(this).addClass("active");  
  
        // slide all elements with the class 'content' up  
        $(".content").slideUp();  
  
        // Now figure out what the 'title' attribute value is and find the element with that id.  Then slide that down.  
        var content_show = $(this).attr("title");  
        $("#"+content_show).slideDown();  
  
    }); 
	};
	bindbehaviors(this);
});

</script>
<?php include 'layout.php'; ?> 
	</head>
	<body>
		<div id="content">
			<div id="tabbed_box_1" class="tabbed_box">  
				<form name="dates" action="monthlyreport.php" method="post">
					<table>
						<tr>
							<td>
								Start Date:<br>
								
							<?php 
							if (strlen($_POST["startdate"])>1)
							{
							 ?><input class="datepicker" type="text" name="startdate" value="<?=$_POST["startdate"]?>"></input><?php	
							}
							else if (isset($_POST["last-3-months"])){
								$now = getdate(time());
				 			 ?>
				 			 <input class="datepicker" type="text" name="startdate" value="<?=strftime('%Y/%m/%d',mktime(0,0,0,$now["mon"]-3,1,2011))?>">
				 			 </input><?php		
							}
							else{
							 ?><input class="datepicker" type="text" name="startdate" value="<?=strftime('%Y/%m/%d',time())?>"></input><?php		
							}?>
							</td>
						</tr>
						<tr>
							<td>
								End Date:<br>
							<?php 
							if (strlen($_POST["enddate"])>1)
							{
							 ?><input class="datepicker" type="text" name="enddate" value="<?=$_POST["enddate"]?>"></input><?php	
							}
							else if (isset($_POST["last-3-months"])){
							 ?><input class="datepicker" type="text" name="startdate" value="<?=strftime('%Y/%m/%d',mktime(23,59,59,$now["mon"],0,2011))?>"></input><?php		
							}
							else {
							 ?><input class="datepicker" type="text" name="enddate" value="<?=strftime('%Y/%m/%d',time())?>"></input><?php		
							}?>
							</td>
							<td>
								<br>
								<input type="submit" name="last-3-months" value="振り返り会議">
							</td>
						</tr>
					</table>
				</form>
			</div>
			<hr>
			<div class="tabbed_area"> 
			<?php
			//For listing Between 2 dates
function listentries($startdate,$enddate)
			{
				$db = mysql_connect("mysql513.in.shared-server.net:16033", "JRtu434", "qvoiceDB1");
				mysql_select_db("JRtu434", $db);
				mysql_query("set SQL_BIG_Selects=1");
				mysql_query("SET NAMES utf8");
				$query = "select RFQS.ID,RFQS.odate,RFQS.subject,companies.NAME,contacts.NAME,RFQS.status,RFQS.genre,LOI,IR,samplesize
				 from RFQS,contacts,genre,companies
				 where RFQS.genre = genre.ID and RFQS.contact = contacts.ID and companies.ID = RFQS.company 
				 and RFQS.odate >= ".$startdate." and RFQS.odate <=".$enddate." order by odate asc";
				$result = mysql_query($query);
				echo "<table border=\"1\">";
				echo "<tr><th>No.</th><th>ID</th><th>Date</th><th>Subject</th><th>Company</th><th>Contact</th><th>Status</th><th>ジャンル</th><th>LOI</th><th>IR</th><th>SS</th></tr>";
				//iterate rows
				$num = 1;
				while ($record = mysql_fetch_array($result))
				{
					echo "<tr><td>$num</td>";
					for($i=0; $i < 10;$i++)
						if($i==1)
							echo "<td>".strftime('%Y/%m/%d  %X',$record[$i])."</td>";
						else
							echo "<td>".$record[$i]."</td>";
					$num++;
					echo "<tr>";
				}
				unset($_POST["list-entries"]);
			}
function monthresults($startmonth,$endmonth)
			{
				$db = mysql_connect("mysql513.in.shared-server.net:16033", "JRtu434", "qvoiceDB1");
				mysql_select_db("JRtu434", $db);
				mysql_query("set SQL_BIG_Selects=1");
				mysql_query("SET NAMES utf8");
				$query =  "select count(ID) from RFQS where odate >= ".$startmonth." and odate <=".$endmonth;
				$result = mysql_query($query);
				$count = mysql_fetch_array($result);
				echo "Displaying results from ".strftime('%y/%m/%d', $startmonth). " til ".strftime('%y/%m/%d',$endmonth)."<br>";
				echo "Total Requests: " . $count[0]."<br>";
				$result = mysql_query("select companies.name,sum(if(RFQS.status=\"p\",1,0)) as pending,
				sum(if(RFQS.status=\"r\",1,0)) as refused,
				sum(if(RFQS.status=\"w\",1,0)) as won,
				sum(if(RFQS.status=\"l\",1,0)) as lost,
				count(RFQS.status) as total
				from RFQS,companies where company=companies.ID and RFQS.odate >= ".strtotime($_POST["startdate"])." and RFQS.odate <=".$endmonth." group by companies.name order by total desc");
				?><table>
					<tr valign="top"><td>
						<table border="1">
							<TR bgcolor="dark-gray" style="color: white;">
								<Th>
									会社名
								</Th>
								<Th>
									返事待ち
								</Th>
								<Th>
									お断り
								</Th>
								<Th>
									受注
								</Th>
								<Th>
									失注
								</Th>
								<th>
									合計
								</th>
							</tr>
				<?php
				$i = 1;
				while ($companycount = mysql_fetch_array($result))
				{
					if($i%2 == 0){
						?><tr bgcolor="yellow" align="center"><?php
					}
					else {
						?><tr align="center"><?php
					}
					?>
					
						<td align="left">
							<?=$companycount["0"]?>
						</td>
						<td>
							<?=$companycount["1"]?>
						</td>
						<td>
							<?=$companycount["2"]?>
						</td>
						<td>
							<?=$companycount["3"]?>
						</td>
						<td>
							<?=$companycount["4"]?>
						</td>
						<td>
							<?=$companycount["5"]?>
						</td>
					</tr>
				<?php
				$i++;	
				}				
				echo "</table></td><td>";
				$result = mysql_query("select genre.NAME,sum(if(RFQS.status=\"p\",1,0)) as pending,
				sum(if(RFQS.status=\"r\",1,0)) as refused,
				sum(if(RFQS.status=\"w\",1,0)) as won,
				sum(if(RFQS.status=\"l\",1,0)) as lost,
				count(RFQS.status) as total
				from RFQS,genre where genre=genre.ID and RFQS.odate >= ".$startmonth." and RFQS.odate <=".$endmonth." group by genre.NAME order by total desc");
				echo "\n";
				?>
				<table border="1">
					<TR bgcolor="dark-gray" style="color: white;">
						<Th>
							ジャンル
						</Th>
						<Th>
							返事待ち
						</Th>
						<Th>
							お断り
						</Th>
						<Th>
							受注
						</Th>
						<Th>
							失注
						</Th>
						<th>
							合計
						</th>
					</tr>
				<?php
				$i = 1;
				while ($companycount = mysql_fetch_array($result))
				{
					if($i%2 == 0){
						?><tr bgcolor="yellow" align="center"><?php
					}
					else {
						?><tr align="center"><?php
					}
					?>
					
						<td align="left">
							<?=$companycount["0"]?>
						</td>
						<td>
							<?=$companycount["1"]?>
						</td>
						<td>
							<?=$companycount["2"]?>
						</td>
						<td>
							<?=$companycount["3"]?>
						</td>
						<td>
							<?=$companycount["4"]?>
						</td>
						<td>
							<?=$companycount["5"]?>
						</td>
					</tr>
				<?php
				$i++;}//ends while for table rows;
				echo "</table></td><td>";
				$result = mysql_query("select LOI,sum(if(RFQS.status=\"p\",1,0)) as pending,
				sum(if(RFQS.status=\"r\",1,0)) as refused,
				sum(if(RFQS.status=\"w\",1,0)) as won,
				sum(if(RFQS.status=\"l\",1,0)) as lost,
				count(RFQS.status) as total
				from RFQS where RFQS.odate >= ".$startmonth." and RFQS.odate <=".$endmonth." group by (floor(LOI/10)) order by LOI asc");
				?>
				<table border="1">
					<TR bgcolor="dark-gray" style="color: white;">
						<Th>
							回答時間
						</Th>
						<Th>
							返事待ち
						</Th>
						<Th>
							お断り
						</Th>
						<Th>
							受注
						</Th>
						<Th>
							失注
						</Th>
						<th>
							合計
						</th>
					</tr>
				<?php
				$i = 1;
				while ($companycount = mysql_fetch_array($result))
				{
					if($i%2 == 0){
						?><tr bgcolor="yellow" align="center"><?php
					}
					else {
						?><tr align="center"><?php
					}
					?>
					
						<td align="left">
							<?=$companycount["0"]?>
						</td>
						<td>
							<?=$companycount["1"]?>
						</td>
						<td>
							<?=$companycount["2"]?>
						</td>
						<td>
							<?=$companycount["3"]?>
						</td>
						<td>
							<?=$companycount["4"]?>
						</td>
						<td>
							<?=$companycount["5"]?>
						</td>
					</tr>
				<?php
				$i++;	
				}				
				echo "</table></td><td>";
				$result = mysql_query("select IR,sum(if(RFQS.status=\"p\",1,0)) as pending,
				sum(if(RFQS.status=\"r\",1,0)) as refused,
				sum(if(RFQS.status=\"w\",1,0)) as won,
				sum(if(RFQS.status=\"l\",1,0)) as lost,
				count(RFQS.status) as total
				from RFQS where RFQS.odate >= ".$startmonth." and RFQS.odate <=".$endmonth." group by (floor(IR/10)) order by IR asc");
				?>
				<table border="1">
					<TR bgcolor="dark-gray" style="color: white;">
						<Th>
							出現率
						</Th>
						<Th>
							返事待ち
						</Th>
						<Th>
							お断り
						</Th>
						<Th>
							受注
						</Th>
						<Th>
							失注
						</Th>
						<th>
							合計
						</th>
					</TR>
				<?php
				$i = 1;
				while ($companycount = mysql_fetch_array($result))
				{
					if($i%2 == 0){
						?><tr bgcolor="yellow" align="center"><?php
					}
					else {
						?><tr align="center"><?php
					}
					?>
					
						<td align="left">
							<?=$companycount["0"]?>
						</td>
						<td>
							<?=$companycount["1"]?>
						</td>
						<td>
							<?=$companycount["2"]?>
						</td>
						<td>
							<?=$companycount["3"]?>
						</td>
						<td>
							<?=$companycount["4"]?>
						</td>
						<td>
							<?=$companycount["5"]?>
						</td>
					</tr>
				<?php
				$i++;	
				}				
		echo "</table></td></tr>";
		//Refused
		$query = "select feasibility,count(feasibility) from RFQS where RFQS.odate >= ".$startmonth." and RFQS.odate <=".$endmonth." group by feasibility
		union select 'Total',count(feasibility) from RFQS where RFQS.odate >= ".$startmonth." and RFQS.odate <=".$endmonth."";
		$result = mysql_query($query);
		echo "<table>";
		while ($record = mysql_fetch_array($result))
		{
			echo "<tr><td>".$record[0]."</td><td>".$record[1]."</td></tr>";
		}
		echo "</table>";
		echo "</table>";
	}//end single request
if (isset($_POST["last-3-months"]))
	{
		$startmonth = strtotime($_POST["startdate"]);
		$endmonth = strtotime($_POST["enddate"]);
		if (date('%y/%m',$startmonth)!=date('%y/%m',$endmonth))
		{
			$date = getdate($startmonth);
			$finalmonth = getdate($endmonth);
			$curmonth = $date["mon"];
			$curyear = $date["year"];
			$months = array($startmonth,mktime(23,59,59,$date["mon"]+1,0,2011));
			$nomonths = ($finalmonth["year"]-$date["year"])*12+($finalmonth["mon"]-$date["mon"]);
			for($i=1;$i<$nomonths;$i++)
				{
					array_push($months,mktime(0,0,0,(($curmonth+$i)==0?12:($curmonth+$i)),1,$curyear),mktime(23,59,59,($curmonth+$i+1),0,$curyear));
					if(($curmonth+$i)>=12)
					{
						$curmonth = $curmonth-12;
						$curyear++;
					}
				}
			array_push($months,mktime(0,0,0,$finalmonth["mon"],1,$finalmonth["year"]),mktime(23,59,59,$finalmonth["mon"],$finalmonth["mday"],$finalmonth["year"]));
		}
		else
			$months = array($startmonth,$endmonth);	
		//Nav-tabs
		echo "<ul class=\"tabs\">";
		echo "<li><a href=\"#\" title=\"content_1\" class=\"tab active\">振り返り会議</a></li>";for($i=0;$i<count($months);$i+=2)
		$i;
		for($i=0;$i<count($months);$i+=2)
		{
			echo "<li><a href=\"#\" title=\"content_".(($i/2)+2)."\" class=\"tab\">".date('m',$months[$i])."</a></li>";
		}
		//Final tab for ulist
		echo "<li><a href=\"#\" title=\"content_".(($i/2)+2)."\" class=\"tab\">List</a></li>";
		echo "</ul>";
		$db = mysql_connect("mysql513.in.shared-server.net:16033", "JRtu434", "qvoiceDB1");
		mysql_select_db("JRtu434", $db);
		mysql_query("set SQL_BIG_Selects=1");
		mysql_query("SET NAMES utf8");
		$query =  "select count(ID) from RFQS where odate >= ".$months[0]." and odate <=".$months[count($months)-1];
		$result = mysql_query($query);
		$count = mysql_fetch_array($result);
		//Start the Furikaeri DIV
		echo "<div id=\"content_1\" class=\"content\">";
		echo "Displaying results from ".strftime('%y-%m-%d-%X',$months[0]). " til ".strftime('%y-%m-%d-%X',$months[count($months)-1])."<br>";
		$query = "select companies.NAME as company,";
		for ($i=0;$i<count($months)-1;$i++)
		{
			$query .= " sum(if(RFQS.status=\"p\", if(odate>=".$months[$i].",if(odate<=".$months[$i+1].",1,0),0),0)) as m".($i+($i/2))."p,\r";
			$query .= " sum(if(RFQS.status=\"r\", if(odate>=".$months[$i].",if(odate<=".$months[$i+1].",1,0),0),0)) as m".($i+($i/2))."r,\r";
			$query .= " sum(if(RFQS.status=\"w\", if(odate>=".$months[$i].",if(odate<=".$months[$i+1].",1,0),0),0)) as m".($i+($i/2))."w,\r";
			$query .= " sum(if(odate>=".$months[$i].", if(odate<=".$months[$i+1].",if(RFQS.status<>\"l\", if(RFQS.subject is not null,1,0),0),0),0)) as m".($i+($i/2))."t,\r";
			$i++;
		}

			$query.=" sum(if(odate>=".$months[0].", if(odate<=".$months[count($months)-1].",if(RFQS.status<>\"l\", if(RFQS.subject is not null,1,0),0),0),0)) as grandtotal, \r";
			$query .= "sum(if(RFQS.status=\"w\", if(odate>=".$months[0].",if(odate<=".$months[count($months)-1].",1,0),0),0)) as wintotal \r";
			$query.=" from RFQS,companies where company=companies.ID and odate>=".$months[0]." and odate<=".$months[count($months)-1]." group by company\r
			union 
			(select 'Total' as company,\r";
		for ($i=0;$i<count($months)-1;$i++)
		{
			$query.= "sum(if(RFQS.status=\"p\", if(odate>=".$months[$i].",if(odate<=".$months[$i+1].",1,0),0),0)) as m".($i+($i/2))."p,\r";
			$query.= "sum(if(RFQS.status=\"r\", if(odate>=".$months[$i].",if(odate<=".$months[$i+1].",1,0),0),0)) as m".($i+($i/2))."r,\r";
			$query.= "sum(if(RFQS.status=\"w\", if(odate>=".$months[$i].",if(odate<=".$months[$i+1].",1,0),0),0)) as m".($i+($i/2))."w,\r";
			$query.= "sum(if(odate>=".$months[$i].", if(odate<=".$months[$i+1].",if(RFQS.status<>\"l\", if(RFQS.subject is not null,1,0),0),0),0)) as m".($i+($i/2))."t,\r";
			$i++;
		}	
			
			$query.="sum(if(odate>=".$months[0].", if(odate<=".$months[count($months)-1].",if(RFQS.status<>\"l\", if(RFQS.subject is not null,1,0),0),0),0)) as grandtotal, \r";
			$query .= "sum(if(RFQS.status=\"w\", if(odate>=".$months[0].",if(odate<=".$months[count($months)-1].",1,0),0),0)) as wintotal \r";
			$query.="from RFQS where odate>=".$months[0]." and odate<=".$months[count($months)-1].")\r
			order by grandtotal desc, company asc";
		$result = mysql_query($query);
		//Opening Tag for page Table
		?><table id="page" border="1"><tr align="center"><td colspan="3"><?php
		//Table for Company x Projects
		echo "<table id=\"company\">\r<thead><tr>\r\t<th rowspan=\"2\" >会社名</th>\r";
		for ($i=0;$i<count($months)-1;$i++)
		{
			echo "\t<th colspan=\"4\" align=\"center\">".strftime('%m',$months[$i])."月</th>\r";
			$i++;
		}
		echo "\t<th rowspan=\"2\">合計</th><th rowspan=\"2\">受注</th><th rowspan=\"2\">受注%</th></tr><tr>";
		for ($i=0;$i<count($months)-1;$i++)
		{
			echo "\t<th>返事待ち</th>\r\t<th>断り</th>\r\t<th>受注</th>\r\t<th>小計</th>\r";
			$i++;
		}
		echo "</tr></thead>";
		$row = 1;
		$tbody = "\t<tbody>\r";
		$tfoot = "\t<tfoot>\r";
		while ($record= mysql_fetch_array($result))
		{
			$winpercent = number_format(($record["wintotal"]*100)/$record["grandtotal"],2);
			if($record[0]=="Total")
				$tfoot .="<tr".($row%2==0?" class=\"odd\"":"").">";
			else 
				$tbody .="<tr".($row%2==0?" class=\"odd\"":"").">";
			$columns = 3+count($months)*2;
			for($i=0; $i<$columns; $i++)
			{
				if($record[0]=="Total")
					$tfoot .="\t<td".($i==0?" class=\"text\">合計":" class=\"number\">$record[$i]")."</td>\r";
				else 
					$tbody .="\t<td".($i==0?" class=\"text\"":" class=\"number\"").">".$record[$i]."</td>\r";
			}
			if($record[0]=="Total")
				$tfoot .="<td>$winpercent%</td></tr>\r";
			else 
				$tbody .="<td>$winpercent%</td></tr>\r";
			$row++;
		}
		$tbody .="\t</tbody>\r";
		$tfoot .="\t</tfoot>\r";
		echo $tfoot ."\r".$tbody;
		echo "</table></td></tr>";
		//Companys X Genre
		echo "<tr align=\"center\"><td colspan=\"3\">";
		echo "Companies x ジャンル (by most companies and most ジャンル)";
		$topcompanies = "select companies.ID, companies.shortname as name, count(company) as total 
		from RFQS,companies where RFQS.company=companies.ID and odate>=$months[0] and odate<=".$months[count($months)-1]." group by name order by total desc,name asc limit 10";
		$topgenres = "select genre.NAME,count(genre) from RFQS,genre where RFQS.genre=genre.ID and
		 odate>=$months[0] and odate<=".$months[count($months)-1]." and genre.NAME<>\"other\" group by genre order by count(genre) desc,genre.Name asc limit 10";
		$result = mysql_query($topgenres);
		$record = mysql_fetch_array($result);
		$genrelist = "(\"".$record[0]."\"";
		while ($record = mysql_fetch_array($result))
			$genrelist .= ", \"".$record[0]."\"";
		$genrelist .=")";
		$result = mysql_query($topcompanies);
		$companycolumns ="";
		while ($record = mysql_fetch_array($result))
			$companycolumns .= ", sum(if(RFQS.company=$record[0],1,0)) as '".$record[1]."'";	
		$query = "select genre.NAME as genres";
		$query .= $companycolumns;
		$query .= ", count(genre) as Total from RFQS,genre where RFQS.genre=genre.ID and odate>=$months[0] and odate<=".$months[count($months)-1].
		" and genre.Name in $genrelist group by genres limit 10";
		$query .= " union select 'Total'";
		$query .= $companycolumns;	
		$query .= ", count(genre) as count from RFQS,genre where RFQS.genre=genre.ID and odate>=$months[0] and odate<=".$months[count($months)-1].
		" order by Total desc";
		$result = mysql_query($query);
		echo "<table id=\"compgenre\">\r<thead><tr>\r\t<th>ジャンル</th>\r";
		$result2 = mysql_query($topcompanies);
		while ($record = mysql_fetch_array($result2))
			echo "<th>".$record[1]."</th>";
		echo "<th>合計</th></tr></thead>";
		$row = 1;
		$tbody = "\t<tbody>\r";
		$tfoot = "\t<tfoot>\r";
		while ($record= mysql_fetch_array($result))
		{
			if($record[0]=="Total")
				$tfoot .="<tr".($row%2==0?" class=\"odd\"":"").">";
			else 
				$tbody .="<tr".($row%2==0?" class=\"odd\"":"").">";
			for($i=0; $i<12; $i++)
			{
				if($record[0]=="Total")
					$tfoot .="\t<td".($i==0?" class=\"text\">合計":" class=\"number\">$record[$i]")."</td>\r";
				else 
					$tbody .="\t<td".($i==0?" class=\"text\"":" class=\"number\"").">".$record[$i]."</td>\r";
			}
			if($record[0]=="Total")
				$tfoot .="</tr>\r";
			else 
				$tbody .="</tr>\r";
			$row++;
		}
		$tbody .="\t</tbody>\r";
		$tfoot .="\t</tfoot>\r";
		echo $tfoot ."\r".$tbody;
		echo "</table>";
		echo "<br>";
		echo "ジャンル x Company wins only";
		$topcompanies = "select companies.ID, companies.shortname as name, sum(if(RFQS.status=\"w\",1,0)) as total 
		from RFQS,companies where RFQS.company=companies.ID and odate>=$months[0] and odate<=".$months[count($months)-1]." group by name order by total desc,name asc limit 10";
		$topgenres = "select genre.NAME,sum(if(RFQS.status =\"w\",1,0)) as total from RFQS,genre where RFQS.genre=genre.ID and
		 odate>=$months[0] and odate<=".$months[count($months)-1]." and genre.NAME<>\"other\" and RFQS.status =\"w\" group by genre order by total desc,name asc limit 10";
		$result = mysql_query($topgenres);
		$record = mysql_fetch_array($result);
		$genrelist = "(\"".$record[0]."\"";
		while ($record = mysql_fetch_array($result))
			$genrelist .= ", \"".$record[0]."\"";
		$genrelist .=")";
		$result = mysql_query($topcompanies);
		$companycolumns ="";
		while ($record = mysql_fetch_array($result))
			$companycolumns .= ", sum(if(RFQS.company=$record[0],if(RFQS.status=\"w\",1,0),0)) as '".$record[1]."'";	
		$query = "select genre.NAME as genres";
		$query .= $companycolumns;
		$query .= ", count(genre) as Total from RFQS,genre where RFQS.genre=genre.ID and odate>=$months[0] and odate<=".$months[count($months)-1].
		" and genre.Name in $genrelist and RFQS.status =\"w\" group by genres limit 10";
		$query .= " union select 'Total'";
		$query .= $companycolumns;	
		$query .= ", count(genre) as count from RFQS,genre where RFQS.genre=genre.ID and odate>=$months[0] and RFQS.status =\"w\" and odate<=".$months[count($months)-1].
		" order by Total desc";
		$result = mysql_query($query);
		echo "<table id=\"compgenrewin\">\r<thead><tr>\r\t<th>ジャンル</th>\r";
		$result2 = mysql_query($topcompanies);
		while ($record = mysql_fetch_array($result2))
			echo "<th>".$record[1]."</th>";
		echo "<th>合計</th></tr></thead>";
		$row = 1;
		$tbody = "\t<tbody>\r";
		$tfoot = "\t<tfoot>\r";
		while ($record= mysql_fetch_array($result))
		{
			if($record[0]=="Total")
				$tfoot .="<tr".($row%2==0?" class=\"odd\"":"").">";
			else 
				$tbody .="<tr".($row%2==0?" class=\"odd\"":"").">";
			for($i=0; $i<12; $i++)
			{
				if($record[0]=="Total")
					$tfoot .="\t<td".($i==0?" class=\"text\">合計":" class=\"number\">$record[$i]")."</td>\r";
				else 
					$tbody .="\t<td".($i==0?" class=\"text\"":" class=\"number\"").">".$record[$i]."</td>\r";
			}
			if($record[0]=="Total")
				$tfoot .="</tr>\r";
			else 
				$tbody .="</tr>\r";
			$row++;
		}
		$tbody .="\t</tbody>\r";
		$tfoot .="\t</tfoot>\r";
		echo $tfoot ."\r".$tbody;
		echo "</table>";
		echo "</td></tr>";
		//ジャンル x Status
		echo "<tr><td colspan=\"3\">";
		$query = "select genre.NAME as genres, ".
		"sum(if(RFQS.status=\"p\",1,0)) as pending, ".
		"sum(if(RFQS.status=\"r\",1,0)) as refused, ".
		"sum(if(RFQS.status=\"w\",1,0)) as won, ".
		"count(RFQS.status) as Total ".
		"from RFQS,genre where RFQS.genre=genre.ID and RFQS.status<>\"l\" and genre.NAME <>\"other\" and odate>=$months[0] and odate<=".$months[count($months)-1].
		" group by genre limit 25".
		" union ".
		"select 'Total', ".
		"sum(if(RFQS.status=\"p\",1,0)) as pending, ".
		"sum(if(RFQS.status=\"r\",1,0)) as refused, ".
		"sum(if(RFQS.status=\"w\",1,0)) as won, ".
		"count(RFQS.status) as Total ".
		"from RFQS,genre where RFQS.genre=genre.ID and RFQS.status<>\"l\" and odate>=$months[0] and odate<=".$months[count($months)-1].
		" order by Total desc,genres asc";
		$result = mysql_query($query);
		echo "<table id=\"genrestatus\">\r<thead><tr>\r\t<th>ジャンル</th>\r";
		echo "<th>返事待ち</th><th>お断り</th><th>受注</th><th>合計</th><th>受注率</th></tr></thead>";
		$row = 1;
		$tbody = "\t<tbody>\r";
		$tfoot = "\t<tfoot>\r";
		while ($record= mysql_fetch_array($result))
		{
			$winpercent = number_format($record["won"]*100/$record["Total"],2);
			if($record[0]=="Total")
				$tfoot .="<tr".($row%2==0?" class=\"odd\"":"").">";
			else 
				$tbody .="<tr".($row%2==0?" class=\"odd\"":"").">";
			for($i=0; $i<5; $i++)
			{
				if($record[0]=="Total")
					$tfoot .="\t<td".($i==0?" class=\"text\">合計":" class=\"number\">$record[$i]")."</td>\r";
				else 
					$tbody .="\t<td".($i==0?" class=\"text\"":" class=\"number\"").">".$record[$i]."</td>\r";
			}
			if($record[0]=="Total")
				$tfoot .="<td>".$winpercent."%</td></tr>\r";
			else 
				$tbody .="<td>".$winpercent."%</td></tr>\r";
			$row++;
		}
		$tbody .="\t</tbody>\r";
		$tfoot .="\t</tfoot>\r";
		echo $tfoot ."\r".$tbody;
		echo "</table></td>";
		echo "</tr>";
		//get the LOI table
		$query = ("select 
		sum(if(LOI >=0,if(LOI<5,1,0),0)) as '0-5分',
		sum(if(LOI >=0,if(LOI<5,if(RFQS.status=\"w\",1,0),0),0)) as '0-5分w',
		sum(if(LOI >=5,if(LOI<10,1,0),0)) as '5-10分',
		sum(if(LOI >=5,if(LOI<10,if(RFQS.status=\"w\",1,0),0),0)) as '5-10分w',
		sum(if(LOI >=10,if(LOI<15,1,0),0)) as '10-15分',
		sum(if(LOI >=10,if(LOI<15,if(RFQS.status=\"w\",1,0),0),0)) as '10-15分w',
		sum(if(LOI >=15,if(LOI<20,1,0),0)) as '15-20分',
		sum(if(LOI >=15,if(LOI<20,if(RFQS.status=\"w\",1,0),0),0)) as '15-20分w',
		sum(if(LOI >=20,if(LOI<25,1,0),0)) as '20-25分',
		sum(if(LOI >=20,if(LOI<25,if(RFQS.status=\"w\",1,0),0),0)) as '20-25分w',
		sum(if(LOI >=25,if(LOI<30,1,0),0)) as '25-30分',
		sum(if(LOI >=25,if(LOI<30,if(RFQS.status=\"w\",1,0),0),0)) as '25-30分w',
		sum(if(LOI >=30,1,0)) as '30分+',
		sum(if(LOI >=30,if(RFQS.status=\"w\",1,0),0)) as '30分+w',
		sum(if(LOI>=0,1,0)) as 'Total',
		sum(if(LOI>=0,if(RFQS.status=\"w\",1,0),0)) as 'Totalw'
		from RFQS where odate between ".$months[0]." and ".$months[count($months)-1]." and status<>\"l\" and subject is not null");
		//make a new row for the LOI table
		echo "<tr align=\"center\"><td>";
		//LOI Table
		$result = mysql_query($query);
		$record = mysql_fetch_array($result);
		echo "<table id=\"LOI\"> <thead><tr><th>回答時間</th><th>件数</th><th>受注</th><th>％</th><th>受注％</th></thead></tr>";		
		$total = $record["Total"];
		$totalw = $record["Totalw"];
		$row=0;
		$tbody = "\t<tbody>\r";
		$tfoot = "\t<tfoot>\r";
		$i=0;
		while((next($record))) 
		 {
		 	$key = key($record);
			$value = $record[$i];
			$value2 = $record[$i+1];
		 	if($key=="Total" || $key=="Totalw"){
					$tfoot.="<tr".($row%2==0?" class=\"odd\"":"").">\r";
					$tfoot.="<td class=\"text\">合計</td>\r<td class=\"number\">$value</td>\r<td class=\"number\">$value2</td>\r
					 <td class=\"number\">".number_format(($value*100)/$total,2)."%</td><td class=\"number\">".number_format(($value2*100)/$totalw,2)."%</td>\r";
				}
				else{
					$tbody.="<tr".($row%2==0?" class=\"odd\"":"").">\r";
					$tbody.="<td class=\"text\">$key</td>\r<td class=\"number\">$value</td>\r<td class=\"number\">$value2</td>\r
					<td class=\"number\">".number_format(($value*100)/$total,2)."%</td>\r<td class=\"number\">".number_format(($value2*100)/$totalw,2)."%</td>\r";
				}
				$row++;
			next($record);
			next($record);
			next($record);
			$i += 2;
		}
		$tbody .="\t</tbody>\r";
		$tfoot .="\t</tfoot>\r";
		echo $tfoot ."\r".$tbody;
		echo "</table></td>";
		//IR Table　-- New cell in page Table
		echo "<td>";
		$query = ("select 
		sum(if(IR >=0,if(IR<5,1,0),0)) as '0-5%',
		sum(if(IR >=0,if(IR<5,if(RFQS.status=\"w\",1,0),0),0)) as '0-5%w',
		sum(if(IR >=5,if(IR<10,1,0),0)) as '5-10%',
		sum(if(IR >=5,if(IR<10,if(RFQS.status=\"w\",1,0),0),0)) as '5-10%w',
		sum(if(IR >=10,if(IR<15,1,0),0)) as '10-15%',
		sum(if(IR >=10,if(IR<15,if(RFQS.status=\"w\",1,0),0),0)) as '10-15%w',
		sum(if(IR >=15,if(IR<20,1,0),0)) as '15-20%',
		sum(if(IR >=15,if(IR<20,if(RFQS.status=\"w\",1,0),0),0)) as '15-20%w',
		sum(if(IR >=20,if(IR<40,1,0),0)) as '20-40%',
		sum(if(IR >=20,if(IR<40,if(RFQS.status=\"w\",1,0),0),0)) as '20-40%w',
		sum(if(IR >=40,if(IR<75,1,0),0)) as '40-75%',
		sum(if(IR >=40,if(IR<75,if(RFQS.status=\"w\",1,0),0),0)) as '40-75%w',
		sum(if(IR >=75,1,0)) as '75-100%',	
		sum(if(IR >=75,if(RFQS.status=\"w\",1,0),0)) as '75-100%w',	
		sum(if(IR is not null,1,0)) as 'Total',
		sum(if(IR is not null,if(RFQS.status=\"w\",1,0),0)) as 'Totalw'
		from RFQS where odate between ".$months[0]." and ".$months[count($months)-1]." and status<>\"l\" and subject is not null");
		echo "<table id=\"IR\"> <thead><tr><th>出現率</th><th>件数</th><th>受注</th><th>％</th><th>受注％</th></thead></tr>";		
		$result = mysql_query($query);
		$record = mysql_fetch_array($result);
		$total = $record["Total"];
		$totalw = $record["Totalw"];
		$row=0;
		$tbody = "\t<tbody>\r";
		$tfoot = "\t<tfoot>\r";
		$i=0;
		while(($element = next($record))) 
		 {
		 	$key = key($record);
			$value = $record[$i];
			$value2 = $record[$i+1];
		 	if($key=="Total" || $key=="Totalw"){
					$tfoot.="<tr".($row%2==0?" class=\"odd\"":"").">\r";
					$tfoot.="<td class=\"text\">合計</td>\r<td class=\"number\">$value</td>\r<td class=\"number\">$value2</td>\r
					 <td class=\"number\">".number_format(($value*100)/$total,2)."%</td><td class=\"number\">".number_format(($value2*100)/$totalw,2)."%</td>\r";
				}
				else{
					$tbody.="<tr".($row%2==0?" class=\"odd\"":"").">\r";
					$tbody.="<td class=\"text\">$key</td>\r<td class=\"number\">$value</td>\r<td class=\"number\">$value2</td>\r
					<td class=\"number\">".number_format(($value*100)/$total,2)."%</td>\r<td class=\"number\">".number_format(($value2*100)/$totalw,2)."%</td>\r";
				}
				$row++;
			$element = next($record);
			$element = next($record);
			$element = next($record);
			$i += 2;
		}
		$tbody .="\t</tbody>\r";
		$tfoot .="\t</tfoot>\r";
		echo $tfoot ."\r".$tbody;
		echo "</table></td>";
		//SSTable
		echo"<td>";
		$query = ("select 
		sum(if(samplesize<25,1,0)) as '0-25',
		sum(if(samplesize >=0,if(samplesize<25,if(RFQS.status=\"w\",1,0),0),0)) as '0-25w',
		sum(if(samplesize >=25,if(samplesize<50,1,0),0)) as '25-50',
		sum(if(samplesize >=25,if(samplesize<50,if(RFQS.status=\"w\",1,0),0),0)) as '25-50w',
		sum(if(samplesize >=50,if(samplesize<100,1,0),0)) as '50-100',
		sum(if(samplesize >=50,if(samplesize<100,if(RFQS.status=\"w\",1,0),0),0)) as '50-100w',
		sum(if(samplesize >=100,if(samplesize<300,1,0),0)) as '100-300',
		sum(if(samplesize >=100,if(samplesize<300,if(RFQS.status=\"w\",1,0),0),0)) as '100-300w',
		sum(if(samplesize >=300,if(samplesize<500,1,0),0)) as '300-500',
		sum(if(samplesize >=300,if(samplesize<500,if(RFQS.status=\"w\",1,0),0),0)) as '300-500w',
		sum(if(samplesize >=500,if(samplesize<1000,1,0),0)) as '500-1000',
		sum(if(samplesize >=500,if(samplesize<1000,if(RFQS.status=\"w\",1,0),0),0)) as '500-1000w',
		sum(if(samplesize >=1000,1,0)) as '1000+',	
		sum(if(samplesize >=1000,if(RFQS.status=\"w\",1,0),0)) as '75-100%w',	
		sum(if(samplesize is not null,1,0)) as 'Total',
		sum(if(samplesize is not null,if(RFQS.status=\"w\",1,0),0)) as 'Totalw'
		from RFQS where odate between ".$months[0]." and ".$months[count($months)-1]." and status<>\"l\" and subject is not null");
		echo "<table id=\"sample\"> <thead><tr><th>SS</th><th>件数</th><th>受注</th><th>％</th><th>受注％</th></thead></tr>";		
		$result = mysql_query($query);
		$record = mysql_fetch_array($result);
		$total = $record["Total"];
		$totalw = $record["Totalw"];
		$row=0;
		$tbody = "\t<tbody>\r";
		$tfoot = "\t<tfoot>\r";
		$i=0;
		while(($element = next($record))) 
		 {
		 	$key = key($record);
			$value = $record[$i];
			$value2 = $record[$i+1];
		 	if($key=="Total" || $key=="Totalw"){
					$tfoot.="<tr".($row%2==0?" class=\"odd\"":"").">\r";
					$tfoot.="<td class=\"text\">合計</td>\r<td class=\"number\">$value</td>\r<td class=\"number\">$value2</td>\r
					 <td class=\"number\">".number_format(($value*100)/$total,2)."%</td><td class=\"number\">".number_format(($value2*100)/$totalw,2)."%</td>\r";
				}
				else{
					$tbody.="<tr".($row%2==0?" class=\"odd\"":"").">\r";
					$tbody.="<td class=\"text\">$key</td>\r<td class=\"number\">$value</td>\r<td class=\"number\">$value2</td>\r
					<td class=\"number\">".number_format(($value*100)/$total,2)."%</td>\r<td class=\"number\">".number_format(($value2*100)/$totalw,2)."%</td>\r";
				}
				$row++;
			$element = next($record);
			$element = next($record);
			$element = next($record);
			$i += 2;
		}
		$tbody .="\t</tbody>\r";
		$tfoot .="\t</tfoot>\r";
		echo $tfoot ."\r".$tbody;
		echo "</table></td>";
		//Day of the week Table
		echo "<tr><td>";
		$query = "Select odate from RFQS where odate between ".$months[0]." and ".$months[count($months)-1]." and subject is not null";
		$result = mysql_query($query);
		$days = array("月曜日", "火曜日","水曜日","木曜日","金曜日","土曜日","日曜日","合計");
		$counts = array(0,0,0,0,0,0,0,0);
		while ($record = mysql_fetch_array($result))
		{	
			if(strftime('%y/%d/%m',$record[0])!="11/01/10")
				$counts[(date('N', $record[0])-1)]++;
			else {
				$counts[rand(0,6)]++;
			}
			$counts[7]++;
		}
		echo "<table id=\"weekdays\"><thead><tr><th>曜日</th><th>件数</th><th>％</th></tr>";		
		$row =0;
		$tbody = "\t<tbody>\r";
		$tfoot = "\t<tfoot>\r";
		for ($i=0;$i<8;$i++)
			 {
			 	if($i==7){
					$tfoot.="<tr".($row%2==0?" class=\"odd\"":"").">\r";
					$tfoot.="<td class=\"text\">$days[$i]</td>\r<td class=\"number\">$counts[$i]</td>\r <td class=\"number\">".number_format(($counts[$i]*100)/$counts[7],2)."%</td>\r";
				}
				else{
					$tbody.="<tr".($row%2==0?" class=\"odd\"":"").">\r";
					$tbody.="<td class=\"text\">$days[$i]</td>\r<td class=\"number\">$counts[$i]</td>\r<td class=\"number\">".number_format(($counts[$i]*100)/$counts[7],2)."%</td>\r";
				}
				$row++;
			}
		$tbody .="\t</tbody>\r";
		$tfoot .="\t</tfoot>\r";
		echo $tfoot ."\r".$tbody;
		echo "</table>";
		echo "</td></tr>";
		//Contacts x win %
		$query = "Select contacts.NAME, companies.NAME, count(RFQS.contact), sum(if(RFQS.status=\"w\",1,0)) as wins, sum(if(RFQS.status=\"w\",1,0))/count(RFQS.contact) as \"Win%\"  from RFQS,companies,contacts 
		where contacts.ID=RFQS.contact and contacts.COMPID=companies.ID group by contacts.NAME 
		union
		Select 'Total', '', count(RFQS.contact), sum(if(RFQS.status=\"w\",1,0)) as wins, sum(if(RFQS.status=\"w\",1,0))/count(RFQS.contact) as \"Win%\"  from RFQS,companies,contacts 
		where contacts.ID=RFQS.contact and contacts.COMPID=companies.ID order by wins desc limit 10";
		$result = mysql_query($query);
		
		//close the page table
		echo "</table>";
		echo "</div>";
		$i=0;
		for($i=0;$i<count($months);$i+=2)
		{
			echo "<div id=\"content_".(($i/2)+2)."\" class=\"content\" style=\"display: none;\">";
			echo monthresults($months[$i], $months[($i+1)]);
			echo "</div>";
		}
		echo "<div id=\"content_".(($i/2)+2)."\" class=\"content\" style=\"display: none;\">";
		echo listentries($months[0], $months[(count($months)-1)]);
		echo "</div>";
		unset($_POST["last-3-months"]);
	}
		?>
		</div>
	</div>
	</body>
</html>